from flask import jsonify, render_template, request

from blues.cloud import bp
from database import session_maker

sql = """
SELECT
	y.org AS 'org',
	y.project AS 'org_project',
	x.*
FROM
	(
		SELECT
			a.project,
			ROUND(
				IFNULL(SUM(a.total_cost_cpu), 0) / 100 / 3600,
				0
			) AS 'total_cpu_core_hours',
			ROUND(
				IFNULL(SUM(a.total_cost_mem), 0) / 1024 / 3600,
				0
			) AS 'total_mem_gb_hours',
			IFNULL(SUM(a.total_task_num), 0) AS 'total_tasks',
			ROUND(
				b.data_filelogicallength / 1024 / 1024 / 1024,
				2
			) AS 'data_size_gb',
			ROUND(
				b.project_quota_size / 1024 / 1024 / 1024 / 3,
				0
			) AS 'quota_size_gb',
			ROUND(
				b.data_filelogicallength * 3 / b.project_quota_size,
				4
			) AS 'quota_used_percent'
		FROM
			`abm_saas_ag`.odps_project_info_topn a,
			`abm_saas_ag`.region_odps_project_storage_info_realtime b
		WHERE
			a.project = b.project
		AND a.ds BETWEEN '{}'
		AND '{}'
		GROUP BY
			a.project
	) x
RIGHT JOIN (
	SELECT
		org,
		project,
		instance_id
	FROM
		`op-admin`.cmdb_cloud_resource
	WHERE
		res_type = 'ODPS'
AND
   is_deleted is NULL
) y ON x.project = y.instance_id;
"""


def odps_stats(sql) -> list:
    # 数据库获取，实例维度-ODPS综合查询
    data_li = []
    with session_maker() as session:
        rs = session.execute(sql)  # res是获取的对象
        for row in rs:
            tmp_dict = {
                'org': row[0], 'org_project': row[1], 'project': row[2], 'total_cpu_core_hours': row[3],
                'total_mem_gb_hours': row[4], 'total_tasks': row[5], 'data_size_gb': row[6], 'quota_size_gb': row[7],
                'quota_used_percent': row[8]
            }
            data_li.append(tmp_dict)
    return data_li


@bp.route('/api/v1/cloud/get_odps_stats', methods=['GET'])
def odps_stats_api():
    start = request.args.get('start')
    end = request.args.get('end')
    print(start, end)
    data_li = odps_stats(sql.format(start, end))
    return jsonify({"code": 200, "data": data_li})


# 渲染web页面
@bp.route('/cloud/odps')
def odps_stats_page():
    return render_template('odps_stats.html', active='cloud_odps')


if __name__ == '__main__':
    a = odps_stats(sql)
    print(a)
